﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using EShop.Common;
using EShop.BussinessObjects;
using MySql.Data.MySqlClient;

namespace EShop.DataAccess
{
    public class CDACompanyWorkingHour
    {
        public static List<CompanyWorkingHour> Select(CompanyWorkingHour companyWorkingHour)
        {
            MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            string NotEffective = ((int)cnstDatabase.DBOperationResult.ValueIsNotEffective).ToString();
            con.Open();
            string _cmdText =
                            "SELECT c.* " +
                            "FROM companyworkinghour c " +
                            "WHERE ((c.ID = " + companyWorkingHour.ID.ToString() + ") OR (" + companyWorkingHour.ID.ToString() + " = " + NotEffective + "))" +
                            "AND ((c.CompanyID=" + companyWorkingHour.Company.ID.ToString() + ")OR(" + companyWorkingHour.Company.ID.ToString() + "=" + NotEffective + "))" +
                            "AND ((c.DayOfWeek='" + companyWorkingHour.DayOfWeek + "')OR('" + companyWorkingHour.DayOfWeek + "'='" + NotEffective + "'))" +
                            "ORDER BY c.ID";

            MySqlCommand cmd = new MySqlCommand(_cmdText, con);
            MySqlDataAdapter dataAdapter = new MySqlDataAdapter(cmd);
            DataTable tbl = new DataTable();
            dataAdapter.Fill(tbl);

            List<CompanyWorkingHour> companyWorkingHourList = new List<CompanyWorkingHour>();

            CompanyWorkingHour _companyWorkingHour;
            foreach (DataRow row in tbl.Rows)
            {
                _companyWorkingHour = new CompanyWorkingHour();
                _companyWorkingHour.ID = (row["ID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["ID"]));

                _companyWorkingHour.Company = new Company();
                _companyWorkingHour.Company.ID = (row["CompanyID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["CompanyID"]));

                _companyWorkingHour.DayOfWeek = (row["DayOfWeek"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["DayOfWeek"].ToString()));
                if (row["FromHour"].ToString() != "")
                    _companyWorkingHour.FromHour = Convert.ToDateTime("1999-01-01 " + row["FromHour"]);
                if (row["ToHour"].ToString() != "")
                    _companyWorkingHour.ToHour = Convert.ToDateTime("1999-01-01 " + row["ToHour"]);
                
                companyWorkingHourList.Add(_companyWorkingHour);
            }
            con.Close();
            return companyWorkingHourList;

        }
    }
}